package com.clh.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.clh.dao.ComplaintDao;
import com.clh.entity.Complaint;
import com.clh.entity.Owner;
import com.clh.util.DBUtil;

/**
 * 
 * @Description: 投诉界面持久层方法体
 * @author 陈志艺
 * @date 2022年5月26日 下午3:33:09
 * @version v1.0
 */
public class ComplaintDaoImpl implements ComplaintDao {
	@Override
	public Complaint findByNo(String comNo) {
		Complaint com = null;
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" comcontent").append(", com_time").append(", com_status").append(", com_handler")
				.append(", room_no").append(" from complaint").append(" where com_no=?");
		ResultSet rs = DBUtil.query(sql.toString(), comNo);
		try {
			while (rs.next()) {
				// comNo = rs.getString("com_no");
				String roomNo = rs.getString("room_no");
				String comTime = rs.getString("com_time");
				String comContent = rs.getString("comcontent");
				String comStatus = rs.getString("com_status");
				String comHandler = rs.getString("com_handler");
				// 创建Complaint对象
				com = new Complaint();
				com.setComNo(comNo);
				com.setComContent(comContent);
				com.setComHandler(comHandler);
				com.setComStatus(Integer.valueOf(comStatus));
				com.setComTime(comTime);
				com.setRoomNo(roomNo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return com;
	}

	@Override
	public List<Complaint> findByPage(String start, String end, String comStatus, String roomNo, int pageNum,
			int pageSize) {
		// 动态sql，可以根据参数情况，拼接不同的sql语句
		// 情况1，没有房间号，查询所有
		// 情况2，时间、房号、状态查询
		List<Complaint> list = new ArrayList<Complaint>();
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" com_no").append(", comcontent").append(", com_time").append(", com_status")
				.append(", com_handler").append(", room_no").append(" from complaint");

		// 动态sql
		if (true) {
			sql.append(" where 1=1");
		}
		//System.out.println(start);
		// 根据参数情况下拼接不同的sql语句
		if (start != null && !"".equals(start)) {
			sql.append(" and date(com_time)>='" + start + "'");
		}
		if (end != null && !"".equals(end)) {
			sql.append(" and date(com_time)<='" + end + "'");
		}
		if (comStatus != null && !"".equals(comStatus)) {
			sql.append(" and com_status='" + comStatus + "'");
		}
		if (roomNo != null && !"".equals(roomNo)) {
			sql.append(" and room_no = '" + roomNo + "'");
		}
		// 分页
		sql.append("  limit  " + (pageNum - 1) * pageSize + "," + pageSize);
		ResultSet rs = DBUtil.query(sql.toString());
		System.out.println(sql.toString());
		try {
			while (rs.next()) {
				roomNo = rs.getString("room_no");
				String comNo = rs.getString("com_no");
				String comTime = rs.getString("com_time");
				String comContent = rs.getString("comcontent");
				comStatus = rs.getString("com_status");
				String comHandler = rs.getString("com_handler");
				// 创建Complaint对象
				Complaint com = new Complaint();
				com.setComNo(comNo);
				com.setComContent(comContent);
				com.setComHandler(comHandler);
				com.setComStatus(Integer.valueOf(comStatus));
				com.setComTime(comTime);
				com.setRoomNo(roomNo);
				list.add(com);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	@Override
	public int countByCondition(String start, String end, String comStatus, String roomNo) {
		int n = 0;
		StringBuilder sql = new StringBuilder();
		sql.append("select").append("  count(*)").append("  from complaint ");
		// 动态SQL
		if (true) {
			sql.append("  where 1=1 ");
		}
		// 判断参数情况拼接sql语句
		if (start != null && !"".equals(start)) {
			sql.append(" and date(com_time)>='" + start + "'");
		}
		if (end != null && !"".equals(end)) {
			sql.append(" and date(com_time)<='" + end + "'");
		}
		if (comStatus != null && !"".equals(comStatus)) {
			sql.append(" and com_status='" + comStatus + "'");

		}
		if (roomNo != null && !"".equals(roomNo)) {
			sql.append(" and room_no = '" + roomNo + "'");
		}
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while (rs.next()) {
				n = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return n;
	}

	@Override
	public List<Complaint> findByCondition(String start, String end, String comStatus, String roomNo, int pageNum,
			int pageSize) {
		// 动态sql，可以根据参数情况，拼接不同的sql语句
		// 情况1，没有房间号，查询所有
		// 情况2，时间、房号、状态查询
		List<Complaint> list = new ArrayList<Complaint>();
		StringBuilder sql = new StringBuilder();
		sql.append("select").append(" com_no").append(", comcontent").append(", com_time").append(", com_status")
				.append(", com_handler").append(", room_no").append(" from complaint");
		// 动态sql
		if (true) {
			sql.append(" where 1=1");
		}
		// 根据参数情况下拼接不同的sql语句
		if (start != null && !"".equals(start)) {
			sql.append(" and date(com_time)>='" + start + "'");
		}
		if (end != null && !"".equals(end)) {
			sql.append(" and date(com_time)<='" + end + "'");
		}
		if (comStatus != null && !"".equals(comStatus)) {
			sql.append(" and com_status='" + comStatus + "'");

		}
		if (roomNo != null && !"".equals(roomNo)) {
			sql.append(" and room_no = '" + roomNo + "'");
		}
		// 分页
		sql.append("  limit  " + (pageNum - 1) * pageSize + "," + pageSize);
		ResultSet rs = DBUtil.query(sql.toString());

		try {
			while (rs.next()) {
				roomNo = rs.getString("room_no");
				String comNo = rs.getString("com_no");
				String comTime = rs.getString("com_time");
				String comContent = rs.getString("comcomtent");
				comStatus = rs.getString("com_status");
				String comHandler = rs.getString("com_handler");
				// 创建Complaint对象
				Complaint com = new Complaint();
				com.setComContent(comContent);
				com.setComHandler(comHandler);
				com.setComNo(comNo);
				com.setComStatus(Integer.parseInt(comStatus));
				com.setComTime(comTime);
				com.setRoomNo(roomNo);
				list.add(com);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	@Override
	public List<Complaint> find() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int insert(Complaint com) {
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into complaint(com_no,comcontent,com_time,com_status,com_handler,room_no)")
				.append(" value(?,?,?,?,?,?)");
		// 调用帮助类实现添加操作
		int n = DBUtil.update(sql.toString(), com.getComNo(), com.getComContent(), com.getComTime(), com.getComStatus(),
				com.getComHandler(), com.getRoomNo());
		return n;
	}

	@Override
	public int update(Complaint com) {
		return DBUtil.update(
				"update complaint set comcontent=?,com_time=?,com_status=?,com_handler=?,room_no=? where com_no=?",
				com.getComContent(), com.getComTime(), com.getComStatus(), com.getComHandler(), com.getRoomNo(),
				com.getComNo());
	}

	@Override
	public int deleteByNo(String comNo) {
		int rs = DBUtil.update("DELETE FROM complaint WHERE com_no=?", comNo);
		return rs;
	}

	@Override
	public List<Owner> countForComplaint(int pageNum,int pageSize) {
		List<Owner> list = new ArrayList<Owner>();
		StringBuffer sql = new StringBuffer();
		sql.append("select count(*),room_no from complaint group by room_no")
		.append(" limit "+(pageNum-1)*pageSize+","+pageSize);
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				Owner o = new Owner();
				o.setRoomNo(rs.getString("room_no"));
				String c = rs.getInt(1)+"";
				o.setOwnerName(c);
				list.add(o);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		
		
		
		
		return list;
	}

}
